This comprehensive reference guide is designed for technical students and database administrators. It provides a structured approach to querying a geographical and travel-oriented dataset.
Key Features:
- β Optimized for MySQL/MariaDB environments (phpMyAdmin)
- β Industry-standard best practices for performance
- β Referential integrity considerations
- β Real-world database scenarios
- β Dataset: 148 photos, 448 ratings, 10 normalized tables
1 Database Schema Overview
The system is built upon 10 normalized tables that manage the relationship between global geography, user engagement, and travel media.
1.1 Core Entity Relationships
As a developer, understanding these mappings is essential for accurate JOIN operations:
- Continents & Countries:
continents.ContinentCode(PK) βcountries.ContinentCode(FK) - Countries & Cities:
countries.CountryCode(PK) βcities.CountryCode(FK) - Users & Content:
users.UserID(PK) βimagedetails.UserID,imagerating.UserID,posts.UserID(FK) - Media Mapping:
imagedetails.ImageID(PK) βimagerating.ImageID,postimages.ImageID(FK) - Geography & Media:
countries.CountryCode(PK) βimagedetails.CountryCode(FK)
Always ensure your foreign keys are indexed. In this dataset of 148 photos and 448 ratings, performance is negligible, but at scale, these indexes prevent full table scans during JOIN operations.
2 Activity 1: Basic Data Retrieval (SELECT)
Mastering the retrieval of raw data from single tables.
2.1 Query Examples
-- Retrieve all records from continents
SELECT * FROM continents;-- Retrieve the first 10 countries ordered alphabetically
SELECT * FROM countries
ORDER BY CountryName ASC
LIMIT 10;-- List images from imagedetails where the location is Canada (using CountryCode 'CA')
SELECT * FROM imagedetails
WHERE CountryCode = 'CA';-- Select all users from the USA (using CountryCode 'US')
SELECT * FROM users
WHERE CountryCode = 'US';-- Find languages starting with 'E'
SELECT * FROM languages
WHERE LanguageName LIKE 'E%';-- Display the top 10 highest-rated records from imagerating
SELECT * FROM imagerating
ORDER BY Rating DESC
LIMIT 10;3 Activity 2: Filtering with WHERE Clause
Utilizing predicates to isolate specific data subsets.
3.1 Query Examples
-- Filter countries for the 'Europe' continent (using ContinentCode 'EU')
SELECT * FROM countries
WHERE ContinentCode = 'EU';-- Select imagerating records with a score > 4
SELECT * FROM imagerating
WHERE Rating > 4;-- Filter countries with a population > 50,000,000
SELECT * FROM countries
WHERE Population > 50000000;-- Find users who joined after 2012
SELECT * FROM users
WHERE JoinDate > '2012-12-31';-- Retrieve imagedetails based on coordinate filters
SELECT * FROM imagedetails
WHERE Latitude BETWEEN 40 AND 50;-- Filter countries with more than 5 neighbors
SELECT * FROM countries
WHERE Neighbors > 5;4 Activity 3: Sorting with ORDER BY
Establishing logical sequence in result sets.
4.1 Query Examples
-- Sort countries by population descending
SELECT * FROM countries
ORDER BY Population DESC;-- Order users alphabetically by last name
SELECT * FROM users
ORDER BY LastName ASC;-- Order countries by area ascending
SELECT * FROM countries
ORDER BY Area ASC;-- Sort imagerating from highest to lowest
SELECT * FROM imagerating
ORDER BY Rating DESC;-- Sort continents alphabetically by name
SELECT * FROM continents
ORDER BY ContinentName ASC;5 Activity 4: Aggregation Functions
Calculating high-level summary statistics.
5.1 Query Examples
-- Count countries per continent (grouped by ContinentCode)
SELECT ContinentCode, COUNT(*) AS TotalCountries
FROM countries
GROUP BY ContinentCode;-- Calculate the global average rating in imagerating
SELECT AVG(Rating) AS GlobalAvgRating
FROM imagerating;-- Find the maximum population
SELECT MAX(Population) AS MaxPopulation
FROM countries;-- Count users grouped by country
SELECT CountryCode, COUNT(*) AS UserCount
FROM users
GROUP BY CountryCode;-- Calculate the average area for countries in the 'Asia' continent ('AS')
SELECT AVG(Area) AS AvgAreaAsia
FROM countries
WHERE ContinentCode = 'AS';-- Count total images associated with each user ID
SELECT UserID, COUNT(*) AS ImageCount
FROM imagedetails
GROUP BY UserID;6 Activity 5: Grouping Data with GROUP BY
Organizing data into categorical buckets for analysis.
6.1 Query Examples
-- Count images per continent (requires JOIN)
SELECT co.ContinentName, COUNT(i.ImageID) AS ImageCount
FROM imagedetails i
JOIN countries c ON i.CountryCode = c.CountryCode
JOIN continents co ON c.ContinentCode = co.ContinentCode
GROUP BY co.ContinentName;-- Calculate average rating per image ID
SELECT ImageID, AVG(Rating) AS AvgRating
FROM imagerating
GROUP BY ImageID;-- Count users per country
SELECT CountryCode, COUNT(*) AS TotalUsers
FROM users
GROUP BY CountryCode;-- Show the count of countries per continent
SELECT ContinentCode, COUNT(CountryCode) AS CountryCount
FROM countries
GROUP BY ContinentCode;-- Count images per city
SELECT CityID, COUNT(*) AS PhotosPerCity
FROM imagedetails
GROUP BY CityID;-- Group ratings by image and provide the total count of ratings per image
SELECT ImageID, COUNT(Rating) AS RatingCount
FROM imagerating
GROUP BY ImageID;7 Activity 6: Filtering Groups with HAVING
Applying conditions to aggregated data.
Remember that WHERE filters rows before aggregation, while HAVING filters the results after the GROUP BY has been performed.
7.1 Query Examples
-- Identify continents with > 30 countries
SELECT ContinentCode, COUNT(*) AS CountryCount
FROM countries
GROUP BY ContinentCode
HAVING COUNT(*) > 30;-- Show images with an average rating > 4
SELECT ImageID, AVG(Rating) AS AvgRating
FROM imagerating
GROUP BY ImageID
HAVING AVG(Rating) > 4;-- List users who have uploaded > 5 images
SELECT UserID, COUNT(*) AS ImageCount
FROM imagedetails
GROUP BY UserID
HAVING COUNT(*) > 5;-- Find countries with > 3 cities recorded
SELECT CountryCode, COUNT(*) AS CityCount
FROM cities
GROUP BY CountryCode
HAVING COUNT(*) > 3;-- Show images that have received > 10 ratings
SELECT ImageID, COUNT(*) AS RatingCount
FROM imagerating
GROUP BY ImageID
HAVING COUNT(*) > 10;8 Activity 7: Simple JOINs (INNER JOIN)
Combining normalized tables to produce human-readable reports.
8.1 Query Examples
-- Connect countries and continents to display continent names
SELECT c.CountryName, con.ContinentName
FROM countries c
INNER JOIN continents con ON c.ContinentCode = con.ContinentCode;-- Join imagedetails and users to display photographer names
SELECT i.ImageID, u.FirstName, u.LastName
FROM imagedetails i
INNER JOIN users u ON i.UserID = u.UserID;-- Join imagedetails, cities, and countries for full context
SELECT i.Title, ci.CityName, co.CountryName
FROM imagedetails i
INNER JOIN cities ci ON i.CityID = ci.CityID
INNER JOIN countries co ON ci.CountryCode = co.CountryCode;-- Join countries with cities to identify capitals
SELECT co.CountryName, ci.CityName AS CapitalName
FROM countries co
INNER JOIN cities ci ON co.CapitalID = ci.CityID;-- Join imagerating with imagedetails
SELECT r.Rating, i.Title
FROM imagerating r
INNER JOIN imagedetails i ON r.ImageID = i.ImageID;-- Join users with userslogin for authentication audit
SELECT u.LastName, l.UserName, l.Password
FROM users u
INNER JOIN userslogin l ON u.UserID = l.UserID;9 Activity 8: Complex JOINs (LEFT, RIGHT)
Preserving records from one side of a relationship even when no match exists.
9.1 Query Examples
-- Identify users with no associated images
SELECT u.FirstName, u.LastName
FROM users u
LEFT JOIN imagedetails i ON u.UserID = i.UserID
WHERE i.ImageID IS NULL;-- Show all images and their ratings (including those with 0 ratings)
SELECT i.Title, r.Rating
FROM imagedetails i
LEFT JOIN imagerating r ON i.ImageID = r.ImageID;-- List all countries and count of images per country (including zero counts)
SELECT c.CountryName, COUNT(i.ImageID) AS PhotoCount
FROM countries c
LEFT JOIN imagedetails i ON c.CountryCode = i.CountryCode
GROUP BY c.CountryName;-- Find cities with no photos
SELECT ci.CityName
FROM cities ci
LEFT JOIN imagedetails i ON ci.CityID = i.CityID
WHERE i.ImageID IS NULL;-- Show all users and their total post count (including users with no posts)
SELECT u.UserName, COUNT(p.PostID) AS TotalPosts
FROM users u
LEFT JOIN posts p ON u.UserID = p.UserID
GROUP BY u.UserName;10 Activity 9: Subqueries
Using the result of one query as the criteria for another.
10.1 Query Examples
-- Find countries with population higher than the global average
SELECT CountryName
FROM countries
WHERE Population > (SELECT AVG(Population) FROM countries);-- List images from the photographer with the highest count of uploads
SELECT *
FROM imagedetails
WHERE UserID = (
SELECT UserID
FROM imagedetails
GROUP BY UserID
ORDER BY COUNT(*) DESC
LIMIT 1
);-- Show countries belonging to the continent with the highest country count
SELECT CountryName
FROM countries
WHERE ContinentCode = (
SELECT ContinentCode
FROM countries
GROUP BY ContinentCode
ORDER BY COUNT(*) DESC
LIMIT 1
);-- Find images taken in cities belonging to France ('FR')
SELECT *
FROM imagedetails
WHERE CityID IN (
SELECT CityID
FROM cities
WHERE CountryCode = 'FR'
);-- List users who have submitted more ratings than the average user
SELECT UserID
FROM imagerating
GROUP BY UserID
HAVING COUNT(*) > (
SELECT AVG(RatingCount)
FROM (
SELECT COUNT(*) AS RatingCount
FROM imagerating
GROUP BY UserID
) AS AverageStats
);Using a derived table ensures accurate average calculation.
11 Activity 10: String Functions
Formatting and manipulating text data for the UI.
11.1 Query Examples
-- Convert country names to uppercase
SELECT UPPER(CountryName) AS UpperCaseName
FROM countries;-- Extract the first 50 characters of image descriptions
SELECT SUBSTRING(Description, 1, 50) AS ShortDesc
FROM imagedetails;-- Find countries containing the string 'island'
SELECT CountryName
FROM countries
WHERE CountryName LIKE '%island%';-- Concatenate user first and last names into a single field
SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM users;-- Return the character count of country descriptions
SELECT CountryName, LENGTH(Description) AS DescLength
FROM countries;12 Activity 11: Date Functions
Calculating intervals and extracting temporal metadata.
12.1 Query Examples
-- Extract the year from user join dates
SELECT YEAR(JoinDate) AS JoinYear
FROM users;-- Calculate registration age in years
SELECT UserName, (YEAR(CURDATE()) - YEAR(JoinDate)) AS YearsRegistered
FROM users;-- Find users who joined in June
SELECT UserName
FROM users
WHERE MONTH(JoinDate) = 6;-- Filter images taken within the last 5 years
SELECT Title
FROM imagedetails
WHERE UploadDate > DATE_SUB(CURDATE(), INTERVAL 5 YEAR);-- Calculate the age of user accounts in days
SELECT UserName, DATEDIFF(CURDATE(), JoinDate) AS DaysActive
FROM users;13 Activity 12: Pattern Matching with LIKE
Advanced wildcard searching.
13.1 Query Examples
-- Find countries starting with 'New'
SELECT CountryName
FROM countries
WHERE CountryName LIKE 'New%';-- List images with 'bridge' in the title
SELECT Title
FROM imagedetails
WHERE Title LIKE '%bridge%';-- Find users with Gmail addresses
SELECT UserName, Email
FROM users
WHERE Email LIKE '%@gmail.com';-- Search for countries containing 'stan'
SELECT CountryName
FROM countries
WHERE CountryName LIKE '%stan%';-- Find cities ending in 'burg'
SELECT CityName
FROM cities
WHERE CityName LIKE '%burg';14 Activity 13: Working with NULL Values
Identifying and managing missing data.
14.1 Query Examples
-- Find countries with no description
SELECT CountryName
FROM countries
WHERE Description IS NULL;-- List images missing GPS coordinates
SELECT Title
FROM imagedetails
WHERE Latitude IS NULL OR Longitude IS NULL;-- Show users with incomplete address fields
SELECT UserName
FROM users
WHERE Address IS NULL;-- Find continents with a null GeoNameId
SELECT ContinentName
FROM continents
WHERE GeoNameId IS NULL;15 Activity 14: Inserting Data
Correctly appending records using explicit column declarations.
Always list your columns in INSERT statements. If the table schema changes later (e.g., adding a nullable column), your code wonβt break.
15.1 Query Examples
-- Insert a new continent
INSERT INTO continents (ContinentCode, ContinentName)
VALUES ('AN', 'Antarctica');-- Add a new user
INSERT INTO users (FirstName, LastName, Email, JoinDate, CountryCode)
VALUES ('Jane', 'Smith', 'j.smith@travel.com', CURDATE(), 'US');-- Insert a rating for an image
INSERT INTO imagerating (ImageID, UserID, Rating)
VALUES (148, 31, 5);-- Add a new country
INSERT INTO countries (CountryCode, CountryName, ContinentCode, Population)
VALUES ('WK', 'Wakanda', 'AF', 5000000);-- Create a new image record
INSERT INTO imagedetails (Title, UserID, CountryCode, UploadDate)
VALUES ('The Hidden City', 31, 'WK', CURDATE());16 Activity 15: Updating Data
Modifying existing records precisely.
16.1 Query Examples
-- Change a specific user's email
UPDATE users
SET Email = 'updated.user@email.com'
WHERE UserID = 1;-- Update a country's population count
UPDATE countries
SET Population = 67000000
WHERE CountryCode = 'FR';-- Modify an image description
UPDATE imagedetails
SET Description = 'A panoramic view of the Eiffel Tower at sunset.'
WHERE ImageID = 5;-- Update a user's privacy setting
UPDATE users
SET PrivacySetting = 1
WHERE UserID = 10;-- Adjust rating values for a specific record
UPDATE imagerating
SET Rating = 5
WHERE RatingID = 100;17 Activity 16: Deleting Data
Safe record removal and constraint awareness.
Attempting to delete a record that is referenced by another table (e.g., deleting a User who has Photos) will trigger a Foreign Key Constraint violation unless ON DELETE CASCADE is configured. Always check dependencies before deletion.
17.1 Query Examples
-- Removing a specific rating
DELETE FROM imagerating
WHERE RatingID = 50;-- Removing a specific user (CAUTION: Referential Integrity)
DELETE FROM users
WHERE UserID = 31;-- Deleting all images for a specific user
DELETE FROM imagedetails
WHERE UserID = 5;-- Removing low ratings
DELETE FROM imagerating
WHERE Rating < 2;18 Activity 17: Complex Queries with Multiple Conditions
Combining logical operators (AND, OR, BETWEEN) with JOINs.
18.1 Query Examples
-- Find European countries with population between 10M and 50M
SELECT c.CountryName
FROM countries c
JOIN continents con ON c.ContinentCode = con.ContinentCode
WHERE con.ContinentName = 'Europe'
AND c.Population BETWEEN 10000000 AND 50000000;-- List images from Canada or USA with rating > 4
SELECT DISTINCT i.Title
FROM imagedetails i
INNER JOIN imagerating r ON i.ImageID = r.ImageID
WHERE (i.CountryCode = 'CA' OR i.CountryCode = 'US')
AND r.Rating > 4;-- Show European users who joined after 2013
SELECT u.UserName
FROM users u
JOIN countries c ON u.CountryCode = c.CountryCode
JOIN continents con ON c.ContinentCode = con.ContinentCode
WHERE con.ContinentName = 'Europe'
AND u.JoinDate > '2013-12-31';-- Find Asian countries with area > 1,000,000 kmΒ²
SELECT c.CountryName
FROM countries c
JOIN continents con ON c.ContinentCode = con.ContinentCode
WHERE con.ContinentName = 'Asia'
AND c.Area > 1000000;19 Activity 18: Using DISTINCT
Eliminating duplicate values for clean reporting.
19.1 Query Examples
-- List unique countries that have associated images
SELECT DISTINCT CountryCode
FROM imagedetails;-- Show unique continents that have registered users
SELECT DISTINCT ContinentCode
FROM users;-- Find unique cities that appear in the imagedetails table
SELECT DISTINCT CityID
FROM imagedetails;-- List unique languages spoken in Europe
SELECT DISTINCT l.LanguageName
FROM languages l
INNER JOIN countries c ON l.LanguageCode = c.LanguageCode
JOIN continents con ON c.ContinentCode = con.ContinentCode
WHERE con.ContinentName = 'Europe';20 Activity 19: Limiting Results
Implementing pagination and identifying βTop-Nβ records.
20.1 Query Examples
-- Show the top 5 most populous countries
SELECT CountryName, Population
FROM countries
ORDER BY Population DESC
LIMIT 5;-- Display the 10 most recent user registrations
SELECT UserName, JoinDate
FROM users
ORDER BY JoinDate DESC
LIMIT 10;-- Get the top 3 highest rated images (by average)
SELECT ImageID, AVG(Rating) AS AvgRating
FROM imagerating
GROUP BY ImageID
ORDER BY AVG(Rating) DESC
LIMIT 3;-- Show the first 20 countries alphabetically
SELECT CountryName
FROM countries
ORDER BY CountryName ASC
LIMIT 20;21 Activity 20: Creating Views
Saving complex logic as virtual tables for reuse.
21.1 Query Examples
-- Countries with their continent names
CREATE OR REPLACE VIEW View_CountryContinents AS
SELECT c.CountryName, con.ContinentName
FROM countries c
JOIN continents con ON c.ContinentCode = con.ContinentCode;-- Images with their average ratings
CREATE OR REPLACE VIEW View_ImageAvgRatings AS
SELECT ImageID, AVG(Rating) AS AvgRating
FROM imagerating
GROUP BY ImageID;-- Users with their total image counts
CREATE OR REPLACE VIEW View_UserImageCounts AS
SELECT UserID, COUNT(*) AS TotalImages
FROM imagedetails
GROUP BY UserID;-- Top-rated image per continent (Deterministic Subquery approach)
CREATE OR REPLACE VIEW View_ContinentTopImages AS
SELECT co.ContinentName, i.ImageID, MAX(r.Rating) AS TopRating
FROM imagerating r
JOIN imagedetails i ON r.ImageID = i.ImageID
JOIN countries c ON i.CountryCode = c.CountryCode
JOIN continents co ON c.ContinentCode = co.ContinentCode
GROUP BY co.ContinentName;22 Activity 21: Data Analysis Project
Synthesis of skills to solve complex business intelligence questions.
22.1 Query Examples
-- Which continent has the most photos?
SELECT co.ContinentName, COUNT(i.ImageID) AS PhotoCount
FROM continents co
JOIN countries c ON co.ContinentCode = c.ContinentCode
JOIN imagedetails i ON c.CountryCode = i.CountryCode
GROUP BY co.ContinentName
ORDER BY PhotoCount DESC
LIMIT 1;-- What is the average rating per continent?
SELECT co.ContinentName, AVG(r.Rating) AS AvgContinentRating
FROM imagerating r
JOIN imagedetails i ON r.ImageID = i.ImageID
JOIN countries c ON i.CountryCode = c.CountryCode
JOIN continents co ON c.ContinentCode = co.ContinentCode
GROUP BY co.ContinentName;-- Who are the top 5 most active photographers?
SELECT u.UserName, COUNT(i.ImageID) AS ImageCount
FROM users u
JOIN imagedetails i ON u.UserID = i.UserID
GROUP BY u.UserName
ORDER BY ImageCount DESC
LIMIT 5;-- Which countries have zero photos?
SELECT c.CountryName
FROM countries c
LEFT JOIN imagedetails i ON c.CountryCode = i.CountryCode
WHERE i.ImageID IS NULL;-- What is the distribution of ratings?
SELECT Rating, COUNT(*) AS Frequency
FROM imagerating
GROUP BY Rating
ORDER BY Rating ASC;-- Which cities are most frequently photographed?
SELECT ci.CityName, COUNT(i.ImageID) AS PhotoCount
FROM cities ci
JOIN imagedetails i ON ci.CityID = i.CityID
GROUP BY ci.CityName
ORDER BY PhotoCount DESC;23 Activity 22: Database Design Analysis
Meta-data querying to understand architectural health.
23.1 Query Examples
-- Identify all foreign keys in the database schema
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'geography_db'
AND REFERENCED_TABLE_NAME IS NOT NULL;-- Identify potential data redundancy (Repeating continent codes in countries)
SELECT ContinentCode, COUNT(*) AS CountryCount
FROM countries
GROUP BY ContinentCode;In a normalized schema, the ContinentName should only exist in the continents table.
-- Demonstrate the many-to-many relationship between posts and images via postimages
SELECT
p.PostID,
p.Title AS PostTitle,
i.ImageID,
i.Title AS ImageTitle
FROM posts p
INNER JOIN postimages pi ON p.PostID = pi.PostID
INNER JOIN imagedetails i ON pi.ImageID = i.ImageID;24 Summary & Best Practices
- Always use explicit column names in
INSERTandSELECTstatements - Index your foreign keys for optimal performance at scale
- Use
WHEREbefore aggregation andHAVINGafterGROUP BY - Test with edge cases including
NULLvalues - Understand referential integrity before deleting data
- Create views for frequently used complex queries
- Use meaningful aliases to improve query readability
- Comment your SQL for future maintainability
This reference guide covers 22 major SQL activities with real-world examples. Practice each section thoroughly to master database querying!
25 Additional Resources
Good Luck on Your Midterm! π
Questions? Review the examples above and practice with the geography database.